﻿using Dapper;
using Hhmocon.Mes.Application.Response;
using Hhmocon.Mes.DataBase.SqlServer;
using Hhmocon.Mes.Repository.Domain;
using Hhmocon.Mes.Repository.Domain.Energy;
using Hhmocon.Mes.Repository.Response;
using Hhmocon.Mes.Util;
using Hhmocon.Mes.Util.AutofacManager;
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Hhmocon.Mes.Repository.Repository
{
    /// <summary>
    /// 碳 页面  
    /// </summary>
    public class EnergyConsumptionRepository : IEnergyConsumptionRepository, IDependency
    {

        public List<energy_consumption_statistics> getStatistics(DateTime date, bool pieChart = false)
        {
            if (date == "0001/1/1 0:00:00".ToDate())
                date = DateTime.Now;
            using (var conn = SqlServerDbHelper.GetConn())
            {
                #region

                List<energy_consumption_statistics> statisticsList = new();

                if (pieChart == false)
                {
                    energy_consumption_statistics statistics = new energy_consumption_statistics();
                    //电的数据
                    statistics.energy_category = 0;
                    ////电当日的数据            
                    //string dsql = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                    //statistics.consumption_td = conn.QueryFirstOrDefault<energy_consumption>(dsql).power_consumption;
                    //statistics.expense_td = conn.QueryFirstOrDefault<energy_consumption>(dsql).power_expense;
                    ////电昨日的数据
                    //string dsql1 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=1 ";
                    //statistics.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(dsql1).power_consumption;
                    //statistics.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(dsql1).power_expense;
                    //电本月累计的数据
                    string dsql2 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                    statistics.consumption_mt = conn.QueryFirstOrDefault<energy_consumption>(dsql2).power_consumption;
                    statistics.expense_mt = conn.QueryFirstOrDefault<energy_consumption>(dsql2).power_expense;
                    //电上月累计的数据
                    string dsql3 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=1 ";
                    statistics.consumption_lmt = conn.QueryFirstOrDefault<energy_consumption>(dsql3).power_consumption;
                    statistics.expense_lmt = conn.QueryFirstOrDefault<energy_consumption>(dsql3).power_expense;
                    //电本年累计数据
                    string dsql4 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(dsql4).power_consumption;
                    statistics.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(dsql4).power_expense;
                    //电上年累计数据
                    string dsql5 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(dsql5).power_consumption;
                    statistics.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(dsql5).power_expense;
                    statisticsList.Add(statistics);

                    energy_consumption_statistics statistics1 = new energy_consumption_statistics();
                    ////气的数据
                    statistics1.energy_category = 1;
                    ////气当日的数据            
                    //string gsql = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                    //statistics1.consumption_td = conn.QueryFirstOrDefault<energy_consumption>(gsql).natural_gas_energy_consumption;
                    //statistics1.expense_td = conn.QueryFirstOrDefault<energy_consumption>(gsql).natural_gas_energy_expense;
                    ////气昨日的数据
                    //string gsql1 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=1 ";
                    //statistics1.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(gsql1).natural_gas_energy_consumption;
                    //statistics1.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(gsql1).natural_gas_energy_expense;
                    //气本月累计的数据
                    string gsql2 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                    statistics1.consumption_mt = conn.QueryFirstOrDefault<energy_consumption>(gsql2).natural_gas_energy_consumption;
                    statistics1.expense_mt = conn.QueryFirstOrDefault<energy_consumption>(gsql2).natural_gas_energy_expense;
                    //气上月累计的数据
                    string gsql3 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=1 ";
                    statistics1.consumption_lmt = conn.QueryFirstOrDefault<energy_consumption>(gsql3).natural_gas_energy_consumption;
                    statistics1.expense_lmt = conn.QueryFirstOrDefault<energy_consumption>(gsql3).natural_gas_energy_expense;
                    //气本年累计数据
                    string gsql4 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics1.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(gsql4).natural_gas_energy_consumption;
                    statistics1.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(gsql4).natural_gas_energy_expense;
                    //气上年累计数据
                    string gsql5 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics1.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(gsql5).natural_gas_energy_consumption;
                    statistics1.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(gsql5).natural_gas_energy_expense;
                    statisticsList.Add(statistics1);


                    energy_consumption_statistics statistics2 = new energy_consumption_statistics();
                    //煤的数据
                    statistics2.energy_category = 2;
                    ////煤当日的数据            
                    //string csql = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                    //statistics2.consumption_td = conn.QueryFirstOrDefault<energy_consumption>(csql).coalr_consumption;
                    //statistics2.expense_td = conn.QueryFirstOrDefault<energy_consumption>(csql).coalr_expense;
                    ////煤昨日的数据
                    //string csql1 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=1 ";
                    //statistics2.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(csql1).coalr_consumption;
                    //statistics2.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(csql1).coalr_expense;
                    //煤本月累计的数据
                    string csql2 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                    statistics2.consumption_mt = conn.QueryFirstOrDefault<energy_consumption>(csql2).coalr_consumption;
                    statistics2.expense_mt = conn.QueryFirstOrDefault<energy_consumption>(csql2).coalr_expense;
                    //煤上月累计的数据
                    string csql3 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=1 ";
                    statistics2.consumption_lmt = conn.QueryFirstOrDefault<energy_consumption>(csql3).coalr_consumption;
                    statistics2.expense_lmt = conn.QueryFirstOrDefault<energy_consumption>(csql3).coalr_expense;
                    //煤本年累计数据
                    string csql4 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics2.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(csql4).coalr_consumption;
                    statistics2.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(csql4).coalr_expense;
                    //煤上年累计数据
                    string csql5 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics2.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(csql5).coalr_consumption;
                    statistics2.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(csql5).coalr_expense;
                    statisticsList.Add(statistics2);

                    energy_consumption_statistics statistics3 = new energy_consumption_statistics();
                    //油的数据
                    statistics3.energy_category = 3;
                    ////油当日的数据            
                    //string osql = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                    //statistics3.consumption_td = conn.QueryFirstOrDefault<energy_consumption>(osql).oil_energy_consumption;
                    //statistics3.expense_td = conn.QueryFirstOrDefault<energy_consumption>(osql).oil_energy_expense;
                    ////油昨日的数据
                    //string osql1 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=1 ";
                    //statistics3.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(osql1).oil_energy_consumption;
                    //statistics1.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(osql1).oil_energy_expense;
                    ////油本月累计的数据
                    string osql2 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                    statistics3.consumption_mt = conn.QueryFirstOrDefault<energy_consumption>(osql2).oil_energy_consumption;
                    statistics3.expense_mt = conn.QueryFirstOrDefault<energy_consumption>(osql2).oil_energy_expense;
                    //油上月累计的数据
                    string osql3 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=1 ";
                    statistics3.consumption_lmt = conn.QueryFirstOrDefault<energy_consumption>(osql3).oil_energy_consumption;
                    statistics3.expense_lmt = conn.QueryFirstOrDefault<energy_consumption>(osql3).oil_energy_expense;
                    //油本年累计数据
                    string osql4 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics3.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(osql4).oil_energy_consumption;
                    statistics3.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(osql4).oil_energy_expense;
                    //油上年累计数据
                    string osql5 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics3.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(osql5).oil_energy_consumption;
                    statistics3.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(osql5).oil_energy_expense;
                    statisticsList.Add(statistics3);




                    //本月
                    energy_consumption_statistics statistics5 = new energy_consumption_statistics();
                    statistics5.consumption_td = statistics.consumption_mt;
                    statistics5.consumption_yd = statistics1.consumption_mt;
                    statistics5.consumption_mt = statistics2.consumption_mt;
                    statistics5.consumption_lmt = statistics3.consumption_mt;
                    statisticsList.Add(statistics5);

                    //上个月
                    energy_consumption_statistics statistics6 = new energy_consumption_statistics();
                    statistics6.consumption_td = statistics.consumption_lmt;
                    statistics6.consumption_yd = statistics1.consumption_lmt;
                    statistics6.consumption_mt = statistics2.consumption_lmt;
                    statistics6.consumption_lmt = statistics3.consumption_lmt;
                    statisticsList.Add(statistics6);

                    //上上个月
                    energy_consumption_statistics statistics7 = new energy_consumption_statistics();

                    string sql = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=2 ";
                    statistics7.consumption_td = conn.QueryFirstOrDefault<energy_consumption>(sql).power_consumption;

                    sql = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=2 ";
                    statistics7.consumption_yd = conn.QueryFirstOrDefault<energy_consumption>(sql).natural_gas_energy_consumption;

                    sql = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=2 ";
                    statistics7.consumption_mt = conn.QueryFirstOrDefault<energy_consumption>(sql).coalr_consumption;

                    sql = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=2 ";
                    statistics7.consumption_lmt = conn.QueryFirstOrDefault<energy_consumption>(sql).oil_energy_consumption;
                    statisticsList.Add(statistics7);
                }
                else
                {

                    energy_consumption_statistics statistics = new energy_consumption_statistics();
                    //电的数据
                    statistics.energy_category = 0;


                    //电本年累计数据
                    string dsql4 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(dsql4).power_consumption;
                    statistics.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(dsql4).power_expense;
                    //电上年累计数据
                    string dsql5 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(dsql5).power_consumption;
                    statistics.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(dsql5).power_expense;


                    energy_consumption_statistics statistics1 = new energy_consumption_statistics();
                    ////气的数据
                    statistics1.energy_category = 1;

                    //气本年累计数据
                    string gsql4 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics1.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(gsql4).natural_gas_energy_consumption;
                    statistics1.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(gsql4).natural_gas_energy_expense;
                    //气上年累计数据
                    string gsql5 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics1.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(gsql5).natural_gas_energy_consumption;
                    statistics1.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(gsql5).natural_gas_energy_expense;


                    energy_consumption_statistics statistics2 = new energy_consumption_statistics();
                    //煤的数据
                    statistics2.energy_category = 2;

                    //煤本年累计数据
                    string csql4 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics2.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(csql4).coalr_consumption;
                    statistics2.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(csql4).coalr_expense;
                    //煤上年累计数据
                    string csql5 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics2.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(csql5).coalr_consumption;
                    statistics2.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(csql5).coalr_expense;

                    energy_consumption_statistics statistics3 = new energy_consumption_statistics();
                    //油的数据
                    statistics3.energy_category = 3;

                    //油本年累计数据
                    string osql4 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                    statistics3.consumption_ye = conn.QueryFirstOrDefault<energy_consumption>(osql4).oil_energy_consumption;
                    statistics3.expense_ye = conn.QueryFirstOrDefault<energy_consumption>(osql4).oil_energy_expense;
                    //油上年累计数据
                    string osql5 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                    statistics3.consumption_lye = conn.QueryFirstOrDefault<energy_consumption>(osql5).oil_energy_consumption;
                    statistics3.expense_lye = conn.QueryFirstOrDefault<energy_consumption>(osql5).oil_energy_expense;

                    ////年统计，懒得改了
                    energy_consumption_statistics statistics8 = new energy_consumption_statistics();
                    statistics8.value = statistics.expense_ye;
                    statistics8.name = "电";
                    statisticsList.Add(statistics8);

                    energy_consumption_statistics statistics9 = new energy_consumption_statistics();
                    statistics9.value = statistics1.expense_ye;
                    statistics9.name = "天然气";
                    statisticsList.Add(statistics9);


                    energy_consumption_statistics statistics10 = new energy_consumption_statistics();
                    statistics10.value = statistics2.expense_ye;
                    statistics10.name = "煤炭";
                    statisticsList.Add(statistics10);

                    energy_consumption_statistics statistics11 = new energy_consumption_statistics();
                    statistics11.value = statistics3.expense_ye;
                    statistics11.name = "石油";
                    statisticsList.Add(statistics11);
                }
                #endregion
                return statisticsList;
            }
        }

        /// <summary>
        /// xx碳排放总量页面接口  时间前端只传年月日  TimeCode 年0月1
        /// </summary>
        /// <returns></returns>
        public List<energy_carbon_emission> GetEnergyConsumption(DateTime StartTime, DateTime EndTime)
        {
            using (var conn = SqlServerDbHelper.GetConn())
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                GetCarbonEmissionResponseTan res = new();
                GetCarbonEmissionResponse resposne = new();
                string sql = $"select * from energy_carbon_emission where create_time between '{StartTime}' and '{EndTime}' and delete_mark = 0";

                //找出时间段内的所有碳排放数据
                List<energy_carbon_emission> energy_Carbon_Emissions = conn.Query<energy_carbon_emission>(sql).ToList();

                return energy_Carbon_Emissions;
            }
        }

        /// <summary>
        /// 获取同比数据，category分别为电气煤油
        /// power_consumption 
        /// natural_gas_energy_consumption
        /// coalr_consumption
        /// oil_energy_consumption
        /// </summary>
        /// <param name="date"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public List<CompareData> getCompareData(DateTime date, string category)
        {
            List<CompareData> compareDatas = new();
            if (date == "0001/1/1 0:00:00".ToDate())
                date = DateTime.Now;
            using (var conn = SqlServerDbHelper.GetConn())
            {
                ////日同比
                //CompareData dayTong = new();
                //string sqlNow = $"select sum({category}) as power_consumption FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                //dayTong.now = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                //string sqlLast = $"select sum({category}) as power_consumption FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=365 ";
                //dayTong.last = conn.QueryFirstOrDefault<energy_consumption>(sqlLast).power_consumption;
                //compareDatas.Add(dayTong);

                ////日环比
                //CompareData dayHuan = new();
                //string sqlNow1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=0 ";
                //dayHuan.now = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                //string sqlLast1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(day,consumption_date,'{date}')=1 ";
                //dayHuan.last = conn.QueryFirstOrDefault<energy_consumption>(sqlLast).power_consumption;
                //compareDatas.Add(dayHuan);

                //月同比
                CompareData monthTong = new();
                string sqlNow2 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                monthTong.now = conn.QueryFirstOrDefault<energy_consumption>(sqlNow2).power_consumption;
                string sqlLast2 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=1 ";
                monthTong.last = conn.QueryFirstOrDefault<energy_consumption>(sqlLast2).power_consumption;
                monthTong.timeNow = date;
                monthTong.timeLast = date.AddYears(-1);
                monthTong.rate = monthTong.last != 0 ? ((monthTong.now - monthTong.last) / monthTong.last) * 100 : 0;
                compareDatas.Add(monthTong);

                //月环比
                CompareData monthHuan = new();
                string sqlNow3 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                monthHuan.now = conn.QueryFirstOrDefault<energy_consumption>(sqlNow3).power_consumption;
                string sqlLast3 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=12 ";
                monthHuan.last = conn.QueryFirstOrDefault<energy_consumption>(sqlLast3).power_consumption;
                monthHuan.timeNow = date;
                monthHuan.timeLast = date.AddMonths(-1);
                monthHuan.rate = monthHuan.last != 0 ? ((monthHuan.now - monthHuan.last) / monthHuan.last) * 100 : 0;
                compareDatas.Add(monthHuan);

                //年比
                CompareData year = new();
                string sqlNow4 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                year.now = conn.QueryFirstOrDefault<energy_consumption>(sqlNow4).power_consumption;
                string sqlLast4 = $"select sum({category}) as power_consumption FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=1 ";
                year.last = conn.QueryFirstOrDefault<energy_consumption>(sqlLast4).power_consumption;
                year.timeNow = date;
                year.timeLast = date.AddYears(-1);
                year.rate = year.last != 0 ? ((year.now - year.last) / year.last) * 100 : 0;
                compareDatas.Add(year);
            }

            return compareDatas;
        }

        public ReturnData getConsumptionStatistics(DateTime startTime, DateTime endTime, string category)
        {

            using (var conn = SqlServerDbHelper.GetConn())
            {
                ReturnData returnData = new();
                List<string> months = new();
                List<string> datas = new();
                List<string> rates = new();


                for (DateTime time = startTime; time <= endTime; time = time.AddMonths(1))
                {

                    months.Add($"{time.Year}-{time.Month}");
                    string sqlNow = $"select  {category} FROM energy_consumption  as power_consumption where consumption_date between  '{time}' and '{time.AddMonths(1)}' and delete_mark = 0 ";
                    var data = conn.QueryFirstOrDefault<energy_consumption>(sqlNow);

                    if (data != null) datas.Add(data.power_consumption.ToString());
                    else datas.Add("0");

                    string sqlNowLast = $"select  {category} FROM energy_consumption  as power_consumption where consumption_date between  '{time.AddMonths(-1)}' and '{time}' and delete_mark = 0 ";
                    var last = conn.QueryFirstOrDefault<energy_consumption>(sqlNowLast) == null ? 0 : conn.QueryFirstOrDefault<energy_consumption>(sqlNowLast).power_consumption;
                    if (last != 0)
                        rates.Add(((data.power_consumption - last) * 100 / last).ToString());
                    else
                        rates.Add("0");
                }
                returnData.datas = datas;
                returnData.months = months;
                returnData.rates = rates;

                return returnData;
            }
        }

        /// <summary>
        /// 碳中和率页面
        /// </summary>
        /// <param name="StartTime"></param>
        /// <param name="EndTime"></param>
        /// <returns></returns>
        public List<energy_carbon_emission> getCarbonMedium(DateTime StartTime, DateTime EndTime)
        {

            using (var conn = SqlServerDbHelper.GetConn())
            {
                if (conn.State == ConnectionState.Closed)
                    conn.Open();

                EndTime = new DateTime(EndTime.Year, EndTime.Month, EndTime.Day, 23, 59, 59);

                CarbonMediumResponse resposne = new();
                string sql = $"select * from energy_carbon_emission where consumption_date between '{StartTime}' and '{EndTime}' and delete_mark = 0";

                //找出时间段内的所有碳排放数据
                List<energy_carbon_emission> energy_Carbon_Emissions = conn.Query<energy_carbon_emission>(sql).ToList();


                return energy_Carbon_Emissions;
            }
        }

        public ReturnData getExpensstatistics(DateTime startTime, DateTime endTime, string category)
        {

            using (var conn = SqlServerDbHelper.GetConn())
            {
                ReturnData returnData = new();
                List<string> months = new();
                List<string> datas = new();
                List<string> rates = new();
                //if(startTime==null)
                for (DateTime time = startTime; time <= endTime; time = time.AddMonths(1))
                {
                    months.Add($"{time.Year}-{time.Month}");
                    string sqlNow = $"select  {category} FROM power_expense  as power_expense where consumption_date between  '{time}' and '{time.AddMonths(1)}' and delete_mark = 0";
                    var data = conn.QueryFirstOrDefault<energy_consumption>(sqlNow);
                    if (data != null) datas.Add(data.power_expense.ToString());
                    else datas.Add("0");


                    string sqlNowLast = $"select  {category} FROM energy_consumption  as power_consumption where consumption_date between  '{time.AddMonths(-1)}' and '{time}' and delete_mark = 0 ";
                    var last = conn.QueryFirstOrDefault<energy_consumption>(sqlNowLast) == null ? 0 : conn.QueryFirstOrDefault<energy_consumption>(sqlNowLast).power_consumption;
                    if (last != 0)
                        rates.Add(((data.power_consumption - last) * 100 / last).ToString());
                    else
                        rates.Add("0");
                }
                returnData.datas = datas;
                returnData.months = months;
                return returnData;
            }
        }

        public List<pieData> getPieData(DateTime date)
        {
            List<pieData> Datas = new();
            if (date == "0001/1/1 0:00:00".ToDate())
                date = DateTime.Now;
            using (var conn = SqlServerDbHelper.GetConn())
            {
                pieData statistics = new pieData();

                //电本年累计数据
                string dsql4 = $"select sum(power_consumption) as power_consumption,sum(power_expense)as power_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                statistics.value = conn.QueryFirstOrDefault<energy_consumption>(dsql4).power_expense;
                statistics.name = "电";
                Datas.Add(statistics);

                pieData statistics1 = new pieData();
                //气本年累计数据
                string gsql4 = $"select sum(natural_gas_energy_consumption) as natural_gas_energy_consumption,sum(natural_gas_energy_expense)as natural_gas_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                statistics1.value = conn.QueryFirstOrDefault<energy_consumption>(gsql4).natural_gas_energy_expense;
                statistics1.name = "天然气";
                Datas.Add(statistics1);

                pieData statistics2 = new pieData();
                //煤本年累计数据
                string csql4 = $"select sum(coalr_consumption) as coalr_consumption,sum(coalr_expense)as coalr_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                statistics2.value = conn.QueryFirstOrDefault<energy_consumption>(csql4).coalr_expense;
                statistics2.name = "煤炭";
                Datas.Add(statistics2);

                pieData statistics3 = new pieData();
                //油本年累计数据
                string osql4 = $"select sum(oil_energy_consumption) as oil_energy_consumption,sum(oil_energy_expense)as oil_energy_expense FROM energy_consumption where DATEDIFF(year,consumption_date,'{date}')=0 ";
                statistics3.value = conn.QueryFirstOrDefault<energy_consumption>(osql4).oil_energy_expense;
                statistics3.name = "石油";
                Datas.Add(statistics3);
            }
            return Datas;
        }

        public List<GramData> getExpenseData(DateTime date)
        {
            List<GramData> DataTotal = new();
            if (date == "0001/1/1 0:00:00".ToDate())
                date = DateTime.Now;
             string[] categorys = { "power_expense", "natural_gas_energy_expense", "coalr_expense", "oil_energy_expense" };
            using (var conn = SqlServerDbHelper.GetConn())
            {
                foreach (var category in categorys)
                {
                    GramData Datas = new();
                    Datas.data = new();
                    Datas.time = new();




                    string sqlNow = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date}')=0 ";
                    int data0 = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                    string sqlLast1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date.AddYears(-1)}')=0 ";
                    int data1 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast1).power_consumption;


                    string date0 = date.ToString("yyyy-MM");
                    string date1 = date.AddYears(-1).ToString("yyyy-MM");

                    Datas.time.Add(date0);
                    Datas.time.Add(date1);



                    Datas.data.Add(data0);
                    Datas.data.Add(data1);
                    DataTotal.Add(Datas);
                }
                return DataTotal;
            }
        }
        public List<GramData> getConsumptionData(DateTime date)
            {
            List<GramData> DataTotal = new();
            if (date == "0001/1/1 0:00:00".ToDate())
                    date = DateTime.Now;
            using (var conn = SqlServerDbHelper.GetConn())
            {
                string[] categorys = { "power_expense", "natural_gas_energy_expense", "coalr_expense", "oil_energy_expense" };
                GramData Datas = new();
                Datas.data = new();
                Datas.time = new();
                DateTime date0 = date;
                DateTime date1 = date.AddMonths(-1);
                DateTime date2 = date.AddMonths(-2);

                foreach (var category in categorys)
                {

                    string sqlNow = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date0}')=0 ";
                    int data0 = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                    string sqlLast1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date0}')=0 ";
                    int data1 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast1).power_consumption;
                    string sqlLast2 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date0}')=0 ";
                    int data2 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast2).power_consumption;
                    string sdate0 = date.ToString("MM");
                    string sdate1 = date.AddMonths(-1).ToString("MM");
                    string sdate2 = date.AddMonths(-2).ToString("MM");

                    Datas.time.Add(sdate0);
                    Datas.time.Add(sdate1);
                    Datas.time.Add(sdate2);
                    Datas.data.Add(data0);
                    Datas.data.Add(data1);
                    Datas.data.Add(data2);

                    sqlNow = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date1}')=0 ";
                    data0 = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                    sqlLast1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date1}')=0 ";
                    data1 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast1).power_consumption;
                    sqlLast2 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date1}')=0 ";
                    data2 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast2).power_consumption;
                    Datas.data.Add(data0);
                    Datas.data.Add(data1);
                    Datas.data.Add(data2);

                    sqlNow = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date2}')=0 ";
                    data0 = conn.QueryFirstOrDefault<energy_consumption>(sqlNow).power_consumption;
                    sqlLast1 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date2}')=0 ";
                    data1 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast1).power_consumption;
                    sqlLast2 = $"select sum({category})  as power_consumption FROM energy_consumption where DATEDIFF(month,consumption_date,'{date2}')=0 ";
                    data2 = conn.QueryFirstOrDefault<energy_consumption>(sqlLast2).power_consumption;
                    Datas.data.Add(data0);
                    Datas.data.Add(data1);
                    Datas.data.Add(data2);
                    DataTotal.Add(Datas);
                }
            }
               return DataTotal;
            }
        }
    }

